# -*- coding: UTF-8 -*-
"""
@Project: SQL CLAUSE
@Author: Leaf
@Date: 2021-05-27
@Description: SQL语句拼接
"""

import warnings
from typing import Dict, List


class Base:
    def __init__(self):
        self._set = ""
        self._where = ""
        self._result = ""
        self._values = ""
        self._join = ""
        self._from = ""
        self._having = ""
        self._group_by = ""
        self._order_by = ""
        self._limit = ""

        self._table_name = ""
        self._columns_names = ""
        self._columns_total = 0
        self._values_total = 0

    def where(self, clause: [str, List[str]], join_condition: str = None):
        """
        设置where子句
            :param cause: where子句
            :param join_condition: 多个单独子句拼接时的逻辑连接符
                只支持 AND 和 OR，开启该条件，会自动将之后生成where子句加上()
        """
        brackets = False
        if join_condition and isinstance(join_condition, str) and join_condition.lower() in ["and", "or"]:
            brackets = True
            self._where = f"{self._where} {join_condition.upper()} ("
        if isinstance(clause, str):
            self._where = clause
        elif isinstance(clause, list):
            for index, item in enumerate(clause):
                if item.lower() == "or":
                    if index:
                        self._where = self._where.rstrip(" ").rstrip("AND")
                        self._where = f"{self._where}{item.upper()} "
                else:
                    self._where = f"{self._where}{item} AND "
            self._where = self._where.rstrip(" ").rstrip("AND").rstrip(" ")
        if brackets:
            self._where = f"{self._where})"

    def table(self, name: str, alias:str = None):
        """
        设置需要更新或者插入的表名
            :param name: 表名
        """
        if isinstance(name, str):
            self._table_name = name
        if alias:
            self._table_name = f"{self._table_name} AS {alias}"

    def columns(self, columns:[str, List[str]]):
        """
        设置需要插入的列名称
            :param columns: 列名
        """
        if isinstance(columns, list):
            self._columns_total = len(columns)
            self._columns_names = ",".join(columns)
        elif isinstance(columns, str):
            self._columns_names = columns
            self._columns_total = len(columns.split(","))
        else:
            raise TypeError("设置columns列名错误！无法识别的数据类型！")

    def join(self, join_type:str, table:str, alias:str=None, condition:[str, Dict]=None):
        """
        表连接
            :param join_type: 表联合方式
                JOIN: 如果表中有至少一个匹配，则返回行
                LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
                RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
                FULL JOIN: 只要其中一个表中存在匹配，就返回行
            :param table:表名
            :param alias:表的别名
            :param condition:连接条件
        """
        if (isinstance(join_type, int) and join_type == 2) or \
            (isinstance(join_type, str) and join_type in ["left", "left join"]):
            join_type = "LEFT JOIN"
        elif (isinstance(join_type, int) and join_type == 3) or \
            (isinstance(join_type, str) and join_type in ["right", "right join"]):
            join_type = "RIGHT JOIN"
        elif (isinstance(join_type, int) and join_type == 4) or \
            (isinstance(join_type, str) and join_type in ["full", "full join"]):
            join_type = "FULL JOIN"
        else:
            join_type = "JOIN"

        if table:
            self._join = f"{self._join} {join_type} {table}"
        else:
            raise ValueError("SQL JOIN 操作必须传入联合的表参数！")

        if alias:
            self._join = f"{self._join} AS {alias}"

        if condition:
            if isinstance(condition, str):
                self._join = f"{self._join} ON {condition}"
            elif isinstance(condition, dict):
                self._join = f"{self._join} ON ("
                for k, v in condition.items():
                    v = "'" + v + "'" if isinstance(v, str) else v
                    self._join = f"{self._join}{k} = {v} AND "
                self._join = self._join.rstrip(" ").rstrip("AND").rstrip(" ")
                self._join = f"{self._join})"


class UpdateClause(Base):
    """SQL UPDATE 更新操作"""
    def __init__(self):
        super(UpdateClause, self).__init__()

    def set(self, data: Dict):
        """
        update更新语句，需要更新的对象
            :param data: 需要更新的对象，字典形式，key为需要更新的column，value是需要更新的新值
        """
        if isinstance(data, dict):
            for k, v in data.items():
                v = "'" + v + "'" if isinstance(v, str) else v
                self._set = f"{self._set} {k} = {v},"
            self._set = self._set.rstrip(",")

    def _update(self):
        """
        按照UPDATE操作生成最终的SQL语句表达式
        UPDATE 表名  SET 列名=值, 列名=值  WHERE 查询条件
        """
        result = "UPDATE"
        if self._table_name:
            result = f"{result} {self._table_name}"
        else:
            raise ValueError("SQL UPDATE 操作必须传入table表名，请使用table(name: str)方法传入table表名！")
        if self._set:
            result = f"{result} SET{self._set}"
        else:
            raise ValueError("SQL UPDATE 操作必须传入SET字段，请使用set(data: Dict)方法传入待更新的对象！")
        if self._where:
            result = f"{result} WHERE {self._where}"
        else:
            warnings.warn('SQL UPDATE 操作未传入 WHERE 条件，可能造成数据篡改的风险！', UserWarning)
        return result


class InsertClause(Base):
    """SQL INSERT INTO 插入操作"""
    def __init__(self):
        super(InsertClause, self).__init__()

    def values(self, values_:List[str]):
        """
        SQL待插入的数据
            :param values_: 待插入的数据列表
        """
        if isinstance(values_, list):
            for v in values_:
                v = "'" + v + "'" if isinstance(v, str) else v
                self._values = f"{self._values} {v},"
            self._values_total = len(values_)
        elif isinstance(values_, str):
            self._values = values_
            self._values_total = len(values_.split(","))
        else:
            raise TypeError("设置values更新值错误！无法识别的数据类型！")
        self._values = self._values.rstrip(",")

    def _insert(self):
        """
        按照INSERT INTO操作生成最终的SQL语句表达式
        INSERT INTO 表名 (列名)  VALUES ()
        """
        result = "INSERT INTO"
        if self._table_name:
            result = f"{result} {self._table_name}"
        else:
            raise ValueError("SQL INSERT INTO 操作必须传入table表名，请使用table(name: str)方法传入table表名！")
        if self._columns_names:
            result = f"{result} ({self._columns_names})"
        if self._values:
            result = f"{result} VALUES({self._values})"
        else:
            raise ValueError('SQL INSERT INTO 操作未传入 VALUES ')
        if self._columns_total and self._values_total != self._columns_total:
            warnings.warn('SQL INSERT INTO 操作列名与赋值数目不一致！', UserWarning)
        return result


class DeleteClause(Base):
    """SQL DELETE 插入操作"""
    def __init__(self):
        super(DeleteClause, self).__init__()

    def _delete(self):
        """
        按照 DELETE 操作生成最终的SQL语句表达式
        DELETE FROM 表名 WHERE 条件
        """
        result = "DELETE FROM"
        if self._table_name:
            result = f"{result} {self._table_name}"
        else:
            raise ValueError("SQL DELETE 操作必须传入table表名，请使用table(name: str)方法传入table表名！")
        if self._where:
            result = f"{result} WHERE {self._where}"
        else:
            raise ValueError("SQL DELETE 操作必须传入WHERE查询条件，不允许全表删除！")
        return result


class SelectClause(Base):
    """SQL SELECT 插入操作"""
    def __init__(self):
        super(SelectClause, self).__init__()
        self._distinct = False

    def group_by(self, group:[str, List[str]]):
        """
        配置 SElECT 分组
            :param group: 分组信息
        """
        if isinstance(group, str):
            self._group_by = group
        elif isinstance(group, list):
            self._group_by = ",".join(group)
        else:
            raise TypeError("设置group分组错误，无法识别的数据类型！")

    def having(self, having_:[str, Dict]):
        """
        配置 SElECT 分组过滤
            :param having: 分组过滤信息
        """
        if isinstance(having_, str):
            self._having = having_
        elif isinstance(having_, dict):
            for k, v in having_.items():
                v = "'" + v + "'" if isinstance(v, str) else v
                self._having = f"{self._having}{k}={v},"
            self._having = self._having.rstrip(" ").rstrip(",")
        else:
            raise TypeError("设置group分组错误，无法识别的数据类型！")

    def order_by(self, order:[str, Dict, List]):
        """
        配置 SElECT 排序
            :param order:排序
        """
        if isinstance(order, str):
            self._order = order
        elif isinstance(order, dict):
            for k, v in order.items():
                is_reverse = "ASC" if v else "DESC"
                self._order = f"{self.order}{k} {is_reverse}, "
        elif isinstance(order, list):
            for k in order:
                if isinstance(k, str):
                    self._order = f"{self.order}{k}, "
                elif isinstance(k, list) or isinstance(k, tuple):
                    if len(k) >=2:
                        is_reverse = "ASC" if k[1] else "DESC"
                        self._order = f"{self.order}{k[0]} {is_reverse},"
                    else:
                        self._order = f"{self.order}{k[0]}, "
        else:
            raise TypeError("设置group分组错误，无法识别的数据类型！")
        self._order = self._order.rstrip(" ").rstrip(",")

    def limit(self, limit_:int, start:int=0):
        """
        设置查询返回数量
            :param limit_: 查询总条目数
            :param start: 返回起始索引
        """
        if isinstance(limit_, int) and isinstance(start, int):
            if start > 0:
                self._limit = f"{start}, {limit_}"
            else:
                self._limit = f"{limit_}"
        else:
            raise TypeError("LIMIT 操作参数必须是整数！")

    def distinct(self, distinct_:bool=False):
        """
        设置查询结果是否去重
            :param distinct_: 去重标志位
        """
        self._distinct = distinct_

    def _select(self):
        """
        按照 SELECT 操作生成最终的SQL语句表达式
        SELECT 表名 WHERE 条件
        """
        result = "SELECT "
        if self._distinct:
            result = f"{result}DISTINCT"
        if self._columns_names:
            result = f"{result} {self._columns_names}"
        else:
            result = f"{result} *"
        if self._table_name:
            result = f"{result} FROM {self._table_name}"
        else:
            raise ValueError("SQL SELECT 操作必须传入table表名，请使用table(name: str)方法传入table表名！")
        if self._join:
            result = f"{result} {self._join}"
        if self._where:
            result = f"{result} WHERE {self._where}"
        if self._group_by:
            result = f"{result} GROUP BY {self._group_by}"
        if self._having:
            result = f"{result} HAVING {self._having}"
        if self._limit:
            result = f"{result} LIMIT {self._limit}"
        return result


class SQL(UpdateClause, InsertClause, DeleteClause, SelectClause):
    def __init__(self, type_: str):
        super(SQL, self).__init__()
        self._type = type_

    @property
    def expression(self):
        if self._type.lower() == "update":
            return self._update()
        elif self._type.lower() == "select":
            return self._select()
        elif self._type.lower() == "delete":
            return self._delete()
        elif self._type.lower() == "insert":
            return self._insert()
        else:
            raise ValueError(f"不支持的操作类型：{self._type}!")



sql = SQL("select")
sql.table("t_feed", "u2")
sql.columns(["id","user_id", "feed_type"])
sql.where(["id=feed_id", "or", "b=2"])
sql.distinct(True)
sql.group_by(["id","user_id", "feed_type"])
sql.having({"id":2})
sql.limit(1, 8)
sql.order_by("user_id")
sql.join("inner", "t_user", "u", {"u.user_id":20})

print(sql.expression)
